package br.com.project.persistence;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {

	private static final String DBNAME = "bizzDB";
	private static final int VERSION = 2;
	
	/* Tables */
	public static final String SIMULATION_TABLE = "Simulation";
	public static final String QUESTION_TABLE = "Question";
	public static final String ANSWER_TABLE = "Answer";
	public static final String QUESTION_ANSWER_TABLE = "Question_Answer";
	public static final String SIMULATION_QUESTION_TABLE = "Simulation_Question";
//	public static final String STUDENT_TABLE = "Student";
//	public static final String RESULT_TABLE = "Result";
//	public static final String RESULT_ANSWER_TABLE = "Result_Answer";
//	public static final String RESULT_RESULT_ANSWER_TABLE = "Result_Result_Answer";
	
	/* Colunas da tabela Answer */
	public static final String COL_ANSWER_ID = "id";
	public static final String COL_ANSWER = "answer";
	public static final String COL_RIGHT = "right";
	
	/* Colunas da tabela Question */
	public static final String COL_QUESTION_ID = "id";
	public static final String COL_QUESTION = "question";
	
	/* Colunas da tabela Question_Answer */
	public static final String COL_ANSWER_QA_ID = "id_answer";
	public static final String COL_QUESTION_QA_ID = "id_question";
	
	/* Colunas da tabela Simulation */
	public static final String COL_SIMULATION_ID = "id";
	public static final String COL_TITLE = "title";
	public static final String COL_DESCRIPTION = "description";
	
	/* Colunas da tabela Simulation_Question */
	public static final String COL_SIMULATION_SQ_ID = "id_simulation";
	public static final String COL_QUESTION_SQ_ID = "id_question";
	
//	/* Colunas da tabela Student */
//	public static final String COL_STUDENT_ID = "id";
//	public static final String COL_NAME = "name";
//	public static final String COL_LOGIN = "login";
//	public static final String COL_PASS = "password";
//	
//	/* Colunas da tabela Result */
//	public static final String COL_RESULT_ID = "id";
//	public static final String COL_DATE = "date";
//	public static final String COL_GRADE = "grade";
//	public static final String COL_SIMULATION_FK = "id_simulation_fk";
//	public static final String COL_STUDENT_FK = "id_student_fk";
//	
//	/* Colunas da tabela Result_Answer */
//	public static final String COL_RESULT_ANSWER_ID = "id";
//	public static final String COL_ANSWERED_ID = "answered_id";
//	public static final String COL_QUESTION_FK = "id_question_fk";
//	
//	/* Colunas da tabela Result_Result_Answer */
//	public static final String COL_RESULT_RR_ID = "id_result";
//	public static final String COL_RESULT_ANSWER_RR_ID = "id_result_answer";
	
	public DatabaseHelper(Context context) {
		super(context, DBNAME, null, VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE IF NOT EXISTS " + ANSWER_TABLE 
				+ " (" 
					+ COL_ANSWER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " 
					+ COL_ANSWER + " TEXT, "
					+ COL_RIGHT +" BOOLEAN" 
				+ ")");
		
		db.execSQL("CREATE TABLE IF NOT EXISTS " + QUESTION_TABLE 
				+ " (" 
					+ COL_QUESTION_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " 
					+ COL_QUESTION + " TEXT" 
				+ ")");
		
		db.execSQL("CREATE TABLE IF NOT EXISTS " + SIMULATION_TABLE 
				+ " (" 
					+ COL_SIMULATION_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " 
					+ COL_TITLE + " TEXT, "
					+ COL_DESCRIPTION + " TEXT" 
				+ ")");
		
		db.execSQL("CREATE TABLE IF NOT EXISTS " + QUESTION_ANSWER_TABLE 
				+ " (" 
					+ COL_QUESTION_QA_ID + " INTEGER NOT NULL, " 
					+ COL_ANSWER_QA_ID + " INTEGER NOT NULL, "
					+ "PRIMARY KEY (" + COL_QUESTION_QA_ID + ", " + COL_ANSWER_QA_ID + "), "
					+ "FOREIGN KEY (" + COL_QUESTION_QA_ID + ") REFERENCES " + QUESTION_TABLE + " (" + COL_QUESTION_ID + "), "
					+ "FOREIGN KEY (" + COL_ANSWER_QA_ID + ") REFERENCES " + ANSWER_TABLE + " (" + COL_ANSWER_ID + ") "
				+ ")");
		
		db.execSQL("CREATE TABLE IF NOT EXISTS " + SIMULATION_QUESTION_TABLE 
				+ " (" 
					+ COL_SIMULATION_SQ_ID + " INTEGER NOT NULL, " 
					+ COL_QUESTION_SQ_ID + " INTEGER NOT NULL, "
					+ "PRIMARY KEY (" + COL_SIMULATION_SQ_ID + ", " + COL_QUESTION_SQ_ID + "), "
					+ "FOREIGN KEY (" + COL_SIMULATION_SQ_ID + ") REFERENCES " + SIMULATION_TABLE + " (" + COL_SIMULATION_ID + "), "
					+ "FOREIGN KEY (" + COL_QUESTION_SQ_ID + ") REFERENCES " + QUESTION_TABLE + " (" + COL_QUESTION_ID + ") "
				+ ")");
		
//		db.execSQL("CREATE TABLE IF NOT EXISTS " + STUDENT_TABLE 
//				+ " (" 
//					+ COL_STUDENT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " 
//					+ COL_NAME + " TEXT, "
//					+ COL_LOGIN + " TEXT, "
//					+ COL_PASS + " TEXT" 
//				+ ")");
//		
//		db.execSQL("CREATE TABLE IF NOT EXISTS " + RESULT_TABLE 
//				+ " (" 
//					+ COL_RESULT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " 
//					+ COL_DATE + " DATE, "
//					+ COL_GRADE +" DOUBLE, " 
//					+ COL_SIMULATION_FK + " INTEGER NOT NULL, "
//					+ COL_STUDENT_FK + " INTEGER NOT NULL, "
//					+ "FOREIGN KEY (" + COL_SIMULATION_FK + ") REFERENCES " + SIMULATION_TABLE + " (" + COL_SIMULATION_ID + "), "
//					+ "FOREIGN KEY (" + COL_STUDENT_FK + ") REFERENCES " + STUDENT_TABLE + " (" + COL_STUDENT_ID + ") "
//				+ ")");
//		
//		db.execSQL("CREATE TABLE IF NOT EXISTS " + RESULT_ANSWER_TABLE 
//				+ " (" 
//					+ COL_RESULT_ANSWER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " 
//					+ COL_ANSWERED_ID + " INTEGER, "
//					+ COL_QUESTION_FK + " INTEGER NOT NULL, "
//					+ "FOREIGN KEY (" + COL_QUESTION_FK + ") REFERENCES " + QUESTION_TABLE + " (" + COL_QUESTION_ID + ") "
//				+ ")");
//		
//		db.execSQL("CREATE TABLE IF NOT EXISTS " + RESULT_RESULT_ANSWER_TABLE 
//				+ " (" 
//					+ COL_RESULT_RR_ID + " INTEGER NOT NULL, " 
//					+ COL_RESULT_ANSWER_RR_ID + " INTEGER NOT NULL, "
//					+ "PRIMARY KEY (" + COL_RESULT_RR_ID + ", " + COL_RESULT_ANSWER_RR_ID + "), "
//					+ "FOREIGN KEY (" + COL_RESULT_RR_ID + ") REFERENCES " + RESULT_TABLE + " (" + COL_RESULT_ID + "), "
//					+ "FOREIGN KEY (" + COL_RESULT_ANSWER_RR_ID + ") REFERENCES " + RESULT_ANSWER_TABLE + " (" + COL_RESULT_ANSWER_ID + ") "
//				+ ")");
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
//		db.execSQL("DROP TABLE IF EXISTS " + RESULT_RESULT_ANSWER_TABLE);
//		db.execSQL("DROP TABLE IF EXISTS " + RESULT_ANSWER_TABLE);
//		db.execSQL("DROP TABLE IF EXISTS " + RESULT_TABLE);
//		db.execSQL("DROP TABLE IF EXISTS " + STUDENT_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + SIMULATION_QUESTION_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + QUESTION_ANSWER_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + ANSWER_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + QUESTION_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + SIMULATION_TABLE);

		onCreate(db);

	}

}
